package model
{
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.SQLEvent;
	
	import mx.managers.CursorManager;
	public class Products extends Inventory
	{
		[Bindable] public var arr_result:Array;
		[Bindable] private var _dataProvider:Array;
		// Schema creation statements.
		private static const CREATE_PRODUCTS:String =
			"CREATE TABLE IF NOT EXISTS products (id TEXT, pk BIGINT PRIMARY KEY, description TEXT, category_pk BIGINT, unit_cost NUMBER, uom TEXT, lead_time INTEGER, available_quantity INTEGER, reorder_quantity INTEGER, date_created BIGINT, date_modified BIGINT);"
		private static const CREATE_ADJUSTMENTS:String =
			"CREATE TABLE adjustments (pk BIGINT PRIMARY KEY, product_pk BIGINT, date BIGINT, qty INTEGER, desc TEXT, userid TEXT)";
		private static const CREATE_CATEGORY:String =
			"CREATE TABLE categories (pk BIGINT PRIMARY KEY, name TEXT)";
		private static const CREATE_COMPANY_PROFILE:String =
			"CREATE TABLE company_profile (name TEXT, address TEXT, phone_number TEXT, tin TEXT, header TEXT, footer TEXT)";
		private static const CREATE_PRODUCT_SUPPLIER:String =
			"CREATE TABLE product_suppliers (pk BIGINT, product_pk BIGINT)";
		private static const CREATE_UOM:String =
			"CREATE TABLE uom (uom TEXT)";
		private var sql_statement:SQLStatement;
		
		private var _range_unit_cost:Array = [];
		private var _range_lead_time:Array = [];
		private var _range_available_quantity:Array = [];
		private var _range_reorder_quantity:Array = [];
		private var _range_date_created:Array = [];
		private var _range_date_modified:Array = [];
		
		/**
		 * creates table suppliers if does not exist
		 */
		public function Products()
		{
			CursorManager.setBusyCursor()
			arr_result = new Array();
			super();
			func_create_table(CREATE_PRODUCTS);
			var invadj:InventoryAdjustments = new InventoryAdjustments();
			CursorManager.removeAllCursors();
		}
		
		public function set dataProvider(results:Array):void {
			_dataProvider = results;
		}
		
		public function get dataProvider():Array {
			return _dataProvider;
		}
		 
		/**
		 * create new product 
		*/
		public function func_new(param_product:Product):void {
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			param_product.func_set_sqlstatement(sql_statement,param_product.s_insert_statement);	
		} 
		
		/**
		 * delete product 
		*/
		public function func_delete_row(str_pk:String):void {
			var prod:Product = new Product();
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.text = prod.s_delete_statement;
			sql_statement.parameters[":pk"] = str_pk;
			sql_statement.execute();
			
			var prodsupp:ProductSuppliers = new ProductSuppliers();
			prodsupp.func_remove_suppliers(str_pk);
		}
		
		/**
		 * update product 
		*/
		public function func_update_row(param_product:Product):void {
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			param_product.func_set_sqlstatement(sql_statement,param_product.s_update_statement);
		}
		
		/**
		 * retrieve all products
		*/
		public function func_queryall():void {
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.text = "SELECT * FROM products";
			sql_statement.itemClass = Product;
			sql_statement.execute();
			func_set_results(sql_statement.getResult())
		}
		
		/**
		 * retrieve some products that are low in inventory
		*/
		public function func_querylow():void {
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.text = "SELECT * FROM products WHERE available_quantity <= reorder_quantity";
			sql_statement.itemClass = Product;
			sql_statement.execute();
			func_set_results(sql_statement.getResult());
		}
		
		/**
		 * retrieve some products that have recently been adjusted
		*/
		public function func_adjusted():void {
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.text = "SELECT p.description, p.id, p.pk, p.available_quantity, a.qty FROM products p, adjustments a WHERE a.product_pk = p.pk LIMIT 10";
			sql_statement.execute();
			func_set_results(sql_statement.getResult());
		}
		
		/**
		 * retrieve some products
		*/
		public function func_querysome(args:Array):void {
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			var s_where:String = "";
			for (var item:* in args) {
				if (s_where == "") s_where += " WHERE ";
				else {
					s_where += " AND ";
				}
				if (item == "date_created_between") {
					s_where += " ( ( date_created >= :n_date_from OR :n_date_from = 0 )  AND  ( date_created <= :n_date_to OR :n_date_to = 30000000000000 ) ) ";
					sql_statement.parameters[':n_date_from'] = args[item][0];
					sql_statement.parameters[':n_date_to'] = args[item][1];
				} 
				if (item == "available_quantity_between") {
					s_where += " ( ( available_quantity >= :n_floor AND available_quantity <= :n_ceil )   OR   ( :n_ceil = 0 AND available_quantity >= :n_floor ) ) ";
					sql_statement.parameters[':n_floor'] = args[item][0];
					sql_statement.parameters[':n_ceil'] = args[item][1];
				} else if (item == "available_quantity_at_least") {
					s_where += " available_quantity >= :avail_qty";
					sql_statement.parameters[':avail_qty'] = args[item];
				} else if (item == "available_quantity_at_most") {
					s_where += " available_quantity <= :avail_qty";
					sql_statement.parameters[':avail_qty'] = args[item];
				}
				
				if (item == "id")  {
					s_where += item + " LIKE :" + item;
					sql_statement.parameters[':'+item] = "%" + args[item] + "%";
				}
				
				if (item == "pk")  {
					s_where += item + " LIKE :" + item;
					sql_statement.parameters[':'+item] = "%" + args[item] + "%";
				}
				
				if (item == "description")  {
					s_where += item + " LIKE :" + item;
					sql_statement.parameters[':'+item] = "%" + args[item] + "%";
				}
				
				if (item == "category_pk") {
					s_where += item + " = :" + item;
					sql_statement.parameters[':'+item] = args[item];
				}
				
			}
			sql_statement.text = "SELECT * FROM products" + s_where;
			sql_statement.itemClass = Product;
			sql_statement.execute();
			func_set_results(sql_statement.getResult());
		}
		
		private function func_set_results(result:SQLResult):void {
			if (result.data != null) {
				dataProvider = result.data;
			}
		}
		
		private function hndlr_openconn_insert(evt:SQLEvent):void {
			sql_statement.addEventListener(SQLEvent.RESULT, func_queryall);
			sql_statement.execute();
		}
		
		/**
		* Create the schema in the database. 
		*/
		private function func_create_schema(evt:SQLEvent = null):void
		{
			
				sql_statement.sqlConnection = sql_conn;
				sql_statement.text = CREATE_PRODUCTS;
				sql_conn.addEventListener(SQLEvent.RESULT, hndlr_createschema);
				sql_statement.execute();
			
		}
		
		/**
		* Create the schema in the database [Part III]. 
		*/
		private function hndlr_createschema(evt:SQLEvent):void {
		}
		
		/**
		 * setter for available_quantity range
		*/ 
		public function set available_quantity_between(a_range:Array):void { 
			_range_available_quantity = a_range;
		}
		
		/**
		 * getter for available_quantity
		*/
		public function get available_quantity_between():Array {
			return _range_available_quantity;
		}
		
		/**
		 * setter for date_created range
		*/ 
		public function set date_created_between(a_range:Array):void { 
			_range_date_created = a_range;
		}
		
		/**
		 * getter for date_created range
		*/
		public function get date_created_between():Array {
			return _range_date_created;
		}
		
		/**
		 * setter for lead_time range
		*/ 
		public function set lead_time_between(a_range:Array):void { 
			_range_lead_time = a_range;
		}
		
		/**
		 * getter for lead_time range
		*/
		public function get lead_time_between():Array {
			return _range_lead_time;
		}
		
		/**
		 * setter for reorder_quantity range
		*/ 
		public function set reorder_quantity_between(a_range:Array):void { 
			_range_reorder_quantity = a_range;
		}
		
		/**
		 * getter for reorder_quantity range
		*/
		public function get reorder_quantity_between():Array {
			return _range_reorder_quantity;
		}
		
		/**
		 * setter for unit_cost range
		*/ 
		public function set unit_cost_between(a_range:Array):void { 
			_range_unit_cost = a_range;
		}
		
		/**
		 * getter for unit_cost range
		*/
		public function get unit_cost_between():Array {
			return _range_unit_cost;
		}
	}
}